package com.github.fancyideas.repository;

import com.github.fancyideas.constance.VIP;
import com.github.fancyideas.module.entity.CustomerEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface CustomerRepository extends JpaRepository<CustomerEntity, Long>, JpaSpecificationExecutor<CustomerEntity> {

    Long countByVipIs(VIP vip);

    @Query(nativeQuery = true, value =
            "select tc.customer_id customerId," +
                    "max(tc.customer_name) as  customerName," +
                    "max(tc.phone) as  phone," +
                    "max(tc.vip) as vip," +
                    "case when min(ts.put_in_date) <= min(tsh.put_in_date) or min(tsh.put_in_date) is null " +
                    "then to_char(min(ts.put_in_date),'yyyy-mm-dd hh24:mm:ss') else to_char(min(tsh.put_in_date),'yyyy-mm-dd hh24:mm:ss') " +
                    "end  firstPutInDate, " +
                    "to_char(max(tsh.take_out_date),'yyyy-mm-dd hh24:mm:ss') as lastTakeOutDate, " +
                    "count(ts.customer_id) onShoesNum," +
                    "count(tsh.customer_id) takeShoesNum," +
                    "sum(tsh.cost) accretionCost " +
                    "from t_customer tc " +
                    "left join t_shoes ts on tc.customer_id = ts.customer_id " +
                    "left join t_shoes_his tsh on tc.customer_id = tsh.customer_id " +
                    "where tc.vip = ?1 " +
                    "group by tc.customer_id"
    )
    List<Map<String, Object>> customerInfo(int vip);


}
